The AND-EQUAL Trap

What is AND-EQUAL?

There is a very old feature of Oracle that causes far more problems than it ever solved. One of the first questions asked by a novice SQL tuner is

"Can Oracle use two indexes on a table at the same time?"

The wise answer is "Yes. But..."

If a table contains two or more single column indexes, and two or more of those columns are used in a SQL using equals predicates, then it is possible for Oracle to use all of the indexes on the columns supplied. Oracle will scan each index separately for matching rows and then merge the rowids from each scan, producing a list of rowids that match every index lookup. Oracle calls the feature AND_EQUAL.

AND_EQUAL is used for low volume SQLs, and is not to be confused with Index Join, another multi-index trick for high volume queries.

SELECT *
FROM   my_table
WHERE  column5 = 230
AND    column8 = 'BLORT'


SELECT STATEMENT
	TABLE ACCESS MY_TABLE BY ROWID
		AND-EQUAL
			NON-UNIQUE INDEX RANGE SCAN MY_TABLE_I5
			NON-UNIQUE INDEX RANGE SCAN MY_TABLE_I8

Sounds good? Well it's usually not. What usually happens is that the indexes all return much the same rows and the AND-EQUAL merge does not really narrow the search down much, or one index is much better than the others and we would be better off just using that one.

Consider the following examples on a table with two single column indexes on columns A and B:

• Column A has approximately 20 rows per key, and column B has approximately 1000 rows per key. An AND-EQUAL query will select the 20 rows from index A, 1000 rows from index B, and merge them to get between 0 and 20 rowids to look up. It would have been faster to ignore index B altogether and just look up every row matching index A.
• Column A and column B both have approximately 1000 rows per key, but due to a quirk of the table the two columns are loosely aligned. Oracle will select the 1000 rows from each index, merge them and return close to 1000 rows. It would have been faster just to select one of the indexes and ignore the other one.
• Column A and column B both have approximately 1000 rows per key, and combined they return an average of 5 rows per combined key? Whilst AND-EQUAL would return reasonable performance, a concatenated index on A and B would be much faster still as it would take you straight to the 5 rows you want.

It is a common trap to believe that two single column indexes are (almost) as good as one concatenated index over both columns. This is never the case.

Fortunately, AND-EQUAL is never chosen automatically by the Cost Based Optimizer, so is now rare. It will only happen when:

• You have not calculated statistics on the table (Oracle defaults back to the Rule based optimizer).
• You specify the AND_EQUAL hint, or
• You specify the RULE hint, or
• You have the Optimizer Goal set to RULE (either with a ALTER SESSION command, or it can be set by the DBA.

How do I stop it?

If you see an AND-EQUAL line in your Explain Plan output, check the information above and decide whether AND-EQUAL is really appropriate. If not, calculate the statistics, remove the AND_EQUAL or RULE hint, or if your Optimizer Goal is set to RULE, add a CHOOSE hint to the SQL.

Is AND-EQUAL ever useful?

Hardly ever. Here are some cases where you might use AND-EQUAL.

• A table has two or more columns of similarly average cardinality (say 2 - 100 rows per key) that are each separately indexed. The cardinality of each index is good enough and used often enough to justify the indexes' existence. Normally, a query with equals clauses on each column would be fastest just using one of the indexes. But if the SQL is using only those indexed columns (ie. all cols in the SELECT, WHERE, GROUP BY and HAVING clauses are in those single column indexes) then Oracle can use AND-EQUAL to avoid accessing the table. The AND-EQUAL option without a table access will probably be faster than using one index and accessing the table.

• Table T has 1 million rows. Columns A, B, and C each have approximately 100 rows per value. Any two combined will return on average 10 rows, and three together guarantee uniqueness. Users frequently query on any two or on all three. Bitmap indexes are inappropriate because there are too many unique values (approx 10,000) for each column. Concatenated indexes are inappropriate, as you would need to create at least three: ABC, AC, BC (this overhead is not really compelling, a better example would use four or more columns). AND-EQUAL would be OK here, because a two column query will read 100 rows from each index returning 10 rows from the table. This would be quicker than using a single index and reading the whole 100 rows from the table to filter them down to 10.

• This one is a real example - the only one I have ever seen. A Workers Compensation system records employees' injuries and illnesses. Every injury has 4 properties:

  1. Nature of Injury/Disease: eg. broken bone, burn, bruise, cancer ...
  2. Location of Injury: eg. eye, cranium, upper leg, liver, skin ...
  3. Mechanism of Injury: eg. brick, shovel, nicotine, uranium ...
  4. Agency of Injury: eg. Falling from height, struck by object, passive smoking...
  To maintain a high quality of data, the regulatory authority distributes a list of invalid code combinations. eg. Broken liver, cancer caused by falling from a height, etc. The invalid combinations are always in pairs (ie. never three or four codes together), and are stored in a table called INVALID_INJURY_COMBINATIONS with 4 columns: NATURE, LOCATION, MECHANISM, and AGENCY. There are about 30,000 rows in the table. Every row has two columns null and two columns set, the combination of the two set (not null) columns values is unique.

  Queries on the table are always on two of the four columns, making 6 possible query paths. Six concatenated indexes would be inefficient because for any two columns, there are only 5,000 rows where both columns are null, leaving 25,000 rows in the index of which 20,000 are useless having one null column. Single column indexes are efficient because the four of them average 15,000 rows each with no wastage.

  For each column, there are between 20 and 200 unique values, giving a cardinality of between 75 and 750 rows per index key. Although Bitmap Indexes would provide good performance, they are inefficient because of the number of nulls in each column, and the relatively high number of unique values. With the AND-EQUAL option, any given query on two columns will read between 75 and 750 rows from each index, and merge the results to give either 1 or 0 rows. That's not bad!